/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50142
Source Host           : localhost:3306
Source Database       : power

Target Server Type    : MYSQL
Target Server Version : 50142
File Encoding         : 65001

Date: 2012-08-25 11:55:59
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `sys_department`
-- ----------------------------
DROP TABLE IF EXISTS `sys_department`;
CREATE TABLE `sys_department` (
`department_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`Department_name`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`parent_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`remark`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`department_id`)
)

;

-- ----------------------------
-- Records of sys_department
-- ----------------------------
BEGIN;
INSERT INTO `sys_department` VALUES ('1', '222', '0', '22'), ('2', '333', '0', '33');
COMMIT;

-- ----------------------------
-- Table structure for `sys_dict`
-- ----------------------------
DROP TABLE IF EXISTS `sys_dict`;
CREATE TABLE `sys_dict` (
`dictID`  int(11) NOT NULL DEFAULT 0 COMMENT '编号' ,
`dictNum`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '代码类型' ,
`dictName`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '代码名称' ,
`dictValue`  int(11) NULL DEFAULT NULL COMMENT '代码值' ,
`dictText`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '代码文本' ,
PRIMARY KEY (`dictID`)
)

;

-- ----------------------------
-- Records of sys_dict
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `sys_function`
-- ----------------------------
DROP TABLE IF EXISTS `sys_function`;
CREATE TABLE `sys_function` (
`function_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`module_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ģ' ,
`name`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`remark`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`callback`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`Display_name`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`before_callback`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ִ' ,
`after_callback`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ִ' ,
`status`  int(11) NULL DEFAULT NULL ,
`sort`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`icon`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ͼ' ,
PRIMARY KEY (`function_id`),
FOREIGN KEY (`module_id`) REFERENCES `sys_module` (`module_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
)

;

-- ----------------------------
-- Records of sys_function
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `sys_module`
-- ----------------------------
DROP TABLE IF EXISTS `sys_module`;
CREATE TABLE `sys_module` (
`module_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'ģ' ,
`module_name`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ģ' ,
`path`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ģ' ,
`status`  int(11) NULL DEFAULT NULL COMMENT 'ģ' ,
`parent_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`remark`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ģ' ,
`isMenu`  int(11) NULL DEFAULT NULL ,
`icon`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ͼ' ,
PRIMARY KEY (`module_id`)
)

;

-- ----------------------------
-- Records of sys_module
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `sys_path`
-- ----------------------------
DROP TABLE IF EXISTS `sys_path`;
CREATE TABLE `sys_path` (
`PID`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`PKey`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '键' ,
`PValue`  varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '值,对象名' ,
`PTarget`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '执行目标' ,
`PStatus`  varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态,1可用,2不可用' ,
`PType`  varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '类型,1功能处理,2页面' ,
PRIMARY KEY (`PID`)
)

;

-- ----------------------------
-- Records of sys_path
-- ----------------------------
BEGIN;
INSERT INTO `sys_path` VALUES ('1', '/general', 'com.powerManager.sdk.handlers.GeneralHandler', 'execute', '1', '1'), ('2', '/menuJson', 'com.powerManager.sdk.handlers.MenuHandler', 'loadJSON', '1', '1'), ('3', '/userList', 'com.powerManager.sdk.handlers.UserHandler', 'loadListJSON', '1', '1');
COMMIT;

-- ----------------------------
-- Table structure for `sys_privilege`
-- ----------------------------
DROP TABLE IF EXISTS `sys_privilege`;
CREATE TABLE `sys_privilege` (
`PrivilegeID`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`PrivilegeMaster`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`PrivilegeMasterKey`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`PrivilegeAccess`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`PrivilegeAccessKey`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`PrivilegeOperation`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`PrivilegeID`)
)

;

-- ----------------------------
-- Records of sys_privilege
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `sys_role`
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`role_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`role_name`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`remark`  varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`status`  varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`role_id`)
)

;

-- ----------------------------
-- Records of sys_role
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `sys_role_user`
-- ----------------------------
DROP TABLE IF EXISTS `sys_role_user`;
CREATE TABLE `sys_role_user` (
`user_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`role_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`role_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
)

;

-- ----------------------------
-- Records of sys_role_user
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `sys_rule`
-- ----------------------------
DROP TABLE IF EXISTS `sys_rule`;
CREATE TABLE `sys_rule` (
`rule_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`name`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`is_public`  int(11) NULL DEFAULT NULL ,
`link`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`remark`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`status`  int(11) NULL DEFAULT NULL COMMENT '״̬' ,
`user_type`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`user_value`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`data_type`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`data_value`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`rule_id`)
)

;

-- ----------------------------
-- Records of sys_rule
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `sys_rule_function`
-- ----------------------------
DROP TABLE IF EXISTS `sys_rule_function`;
CREATE TABLE `sys_rule_function` (
`rule_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`module_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ģ' ,
`id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`module_id`) REFERENCES `sys_module` (`module_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`rule_id`) REFERENCES `sys_rule` (`rule_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
)

;

-- ----------------------------
-- Records of sys_rule_function
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `sys_sequence`
-- ----------------------------
DROP TABLE IF EXISTS `sys_sequence`;
CREATE TABLE `sys_sequence` (
`seqNum`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '流水编号' ,
`seqName`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称' ,
`seqValue`  bigint(20) NULL DEFAULT 1 COMMENT '值' ,
`seqPrefix`  varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '前缀' ,
`seqSuffix`  varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '后缀' ,
`seqLength`  int(11) NULL DEFAULT NULL COMMENT '流水号长度' ,
`seqFill`  char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '填充内容' ,
`seqAdd`  int(11) NULL DEFAULT NULL COMMENT '每一次递增值' ,
`seqLastValue`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上一次的值' ,
PRIMARY KEY (`seqNum`)
)

;

-- ----------------------------
-- Records of sys_sequence
-- ----------------------------
BEGIN;
INSERT INTO `sys_sequence` VALUES ('SEQ_TEST', 'TEST', '103', 'KJXM[yy][mm]', 'X', '16', '0', '1', 'KJXM12080000103X');
COMMIT;

-- ----------------------------
-- Table structure for `sys_user`
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`user_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`login_name`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`password`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`department_id`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`nickname`  varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`status`  int(11) NULL DEFAULT NULL ,
`password_status`  int(11) NULL DEFAULT NULL ,
`remark`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`user_id`),
FOREIGN KEY (`department_id`) REFERENCES `sys_department` (`department_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
)

;

-- ----------------------------
-- Records of sys_user
-- ----------------------------
BEGIN;
INSERT INTO `sys_user` VALUES ('1', 'admin', 'admin', '1', 'admin1', '1', '1', '1'), ('2', 'superadmin', 'superadmin', '2', 'admin2', '1', '1', '1');
COMMIT;

-- ----------------------------
-- Function structure for `GET_SEQ`
-- ----------------------------
DROP FUNCTION IF EXISTS `GET_SEQ`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `GET_SEQ`(`seqNum` varchar(50),seqLast varchar(2)) RETURNS varchar(30) CHARSET utf8
BEGIN
	DECLARE v_seqLength int;#流水号长度
	DECLARE tempLength int;#临时流水号长度
	DECLARE v_seqPrefix VARCHAR(25);#流水号前缀
	DECLARE v_seqSuffix VARCHAR(25);#流水号后缀
	DECLARE v_seqFill		VARCHAR(2);#填充值
	DECLARE v_seqValue		VARCHAR(30);#值
	DECLARE v_seqAdd  int;#每一次递增值
	DECLARE retValue  VARCHAR(30);#返回值
	DECLARE v_seqLastValue  VARCHAR(30);#上一次的值
	SELECT ssq.seqFill,ssq.seqLength,ssq.seqPrefix,ssq.seqSuffix,ssq.seqValue,ssq.seqAdd,ssq.seqLastValue
	into v_seqFill,v_seqLength,v_seqPrefix,v_seqSuffix,v_seqValue,v_seqAdd,v_seqLastValue 
	from sys_sequence ssq where ssq.seqNum=seqNum;
	if seqLast='1' and v_seqLastValue is not null THEN
		return v_seqLastValue;
	end if;
	if v_seqPrefix is not null THEN
		#set retValue=v_seqPrefix;
		set v_seqPrefix=REPLACE(v_seqPrefix,'[yyyy]',DATE_FORMAT(NOW(),'%Y'));
		set v_seqPrefix=REPLACE(v_seqPrefix,'[yy]',DATE_FORMAT(NOW(),'%y'));
		set v_seqPrefix=REPLACE(v_seqPrefix,'[mm]',DATE_FORMAT(NOW(),'%m'));
		set v_seqPrefix=REPLACE(v_seqPrefix,'[dd]',DATE_FORMAT(NOW(),'%d'));
		set v_seqPrefix=REPLACE(v_seqPrefix,'[hh]',DATE_FORMAT(NOW(),'%H'));
		set v_seqPrefix=REPLACE(v_seqPrefix,'[ii]',DATE_FORMAT(NOW(),'%i'));
		set v_seqPrefix=REPLACE(v_seqPrefix,'[ss]',DATE_FORMAT(NOW(),'%s'));
		#return v_seqPrefix;
	end if;
	if v_seqSuffix is not null THEN
		#set retValue=v_seqPrefix;
		set v_seqSuffix=REPLACE(v_seqSuffix,'[yyyy]',DATE_FORMAT(NOW(),'%Y'));
		set v_seqSuffix=REPLACE(v_seqSuffix,'[yy]',DATE_FORMAT(NOW(),'%y'));
		set v_seqSuffix=REPLACE(v_seqSuffix,'[mm]',DATE_FORMAT(NOW(),'%m'));
		set v_seqSuffix=REPLACE(v_seqSuffix,'[dd]',DATE_FORMAT(NOW(),'%d'));
		set v_seqSuffix=REPLACE(v_seqSuffix,'[hh]',DATE_FORMAT(NOW(),'%H'));
		set v_seqSuffix=REPLACE(v_seqSuffix,'[ii]',DATE_FORMAT(NOW(),'%i'));
		set v_seqSuffix=REPLACE(v_seqSuffix,'[ss]',DATE_FORMAT(NOW(),'%s'));
	end if;
	set v_seqValue = v_seqValue+v_seqAdd;
	set tempLength=v_seqLength-(LENGTH(v_seqPrefix)+LENGTH(v_seqSuffix)+LENGTH(v_seqValue));
	if tempLength>=0 THEN
		set retValue=CONCAT_WS('',v_seqPrefix,REPEAT(v_seqFill,tempLength),v_seqValue,v_seqSuffix);
		update sys_sequence set seqValue=v_seqValue,seqLastValue=retValue where sys_sequence.seqNum=seqNum;
	ELSE
		set retValue=v_seqLastValue;
	end if;
return retValue;
END
;;
DELIMITER ;

-- ----------------------------
-- Indexes structure for table `sys_dict`
-- ----------------------------
CREATE INDEX `index_sysdict_num` USING BTREE ON `sys_dict`(`dictNum`) ;

-- ----------------------------
-- Indexes structure for table `sys_function`
-- ----------------------------
CREATE INDEX `FK_Reference_4` USING BTREE ON `sys_function`(`module_id`) ;

-- ----------------------------
-- Indexes structure for table `sys_privilege`
-- ----------------------------
CREATE UNIQUE INDEX `PrimaryKey` USING BTREE ON `sys_privilege`(`PrivilegeID`) ;

-- ----------------------------
-- Indexes structure for table `sys_role_user`
-- ----------------------------
CREATE INDEX `FK_Reference_2` USING BTREE ON `sys_role_user`(`user_id`) ;
CREATE INDEX `FK_Reference_3` USING BTREE ON `sys_role_user`(`role_id`) ;

-- ----------------------------
-- Indexes structure for table `sys_rule_function`
-- ----------------------------
CREATE INDEX `FK_Reference_7` USING BTREE ON `sys_rule_function`(`module_id`) ;
CREATE INDEX `FK_Reference_8` USING BTREE ON `sys_rule_function`(`rule_id`) ;

-- ----------------------------
-- Indexes structure for table `sys_user`
-- ----------------------------
CREATE INDEX `FK_Reference_1` USING BTREE ON `sys_user`(`department_id`) ;
